1. Field
Embodiments of the invention relate to statement generation using statement patterns.
2. Description of the Related Art
Relational DataBase Management System (RDBMS) software uses a Structured Query Language (SQL) interface. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
RDBMS uses relational techniques for storing and retrieving data in a relational database. Relational databases are computerized information storage and retrieval systems. Relational databases are organized into tables that consist of rows and columns of data. The rows may be called tuples or records or rows. A database typically has many tables, and each table typically has multiple records and multiple columns.
SQL is an ANSI/ISO standard language that users can utilize to create, retrieve, update, and delete data that resides in relational databases. SQL is commonly used by applications in order to interact with relational databases. A majority of SQL queries are similar in their structure and format, varying only in predicates or number of parameters. These variations occur due to a variety of factors, including optimistic concurrency control, paging (e.g., retrieving a first page or some other page in a set of pages), and conditional logic. Under an optimistic concurrency control scheme, locks are obtained immediately before a read operation and released immediately afterwards and update locks are obtained immediately before an update operation and held until the end of the transaction. Conditional logic refers to a WHERE clause in SQL statement (e.g., a user has two SQL statements with the same columns in the SELECT list but with a different number of host variables in the WHERE clause).
By itself, SQL lacks portability (i.e., the same SQL query might not work against different databases). One reason for the lack of portability in SQL is that some relational database vendors either do not follow the SQL standard or do not incorporate the entire standard. Another reason for lack of portability in SQL is that relational database vendors may choose to implement certain features of SQL differently because the SQL standard is not explicit in certain areas. These issues force an application programmer to write slightly different versions of an original SQL query in order to satisfy the syntactic requirements of each relational database the SQL query is supposed to run against.
Some cross-platform applications utilize string concatenation to build different flavors of the same original SQL query at runtime. The main drawback of this technique is that it leaves no room for static execution. Static execution requires that a statement is available prior to the runtime so that the statement can be prepared and bound to the database. If String concatenation is used, the actual String is only available at runtime, thereby leaving no room for static execution. Building different flavors may be described as building multiple SQL queries out of an original SQL query, where each of the SQL queries is compatible with a different RDBMS version (i.e., each of the multiple SQL statements is a flavor). Preparing and binding the SQL statement may be described as processing the SQL statement to get an access plan and registering the statement and access plan with the database.
Thus, application programs have the daunting task of writing one variation of the original SQL query for each relational database that the SQL query needs to run against. That is, application programmers are forced to learn various flavors and quirks of several flavors of SQL. The task of learning several SQL flavors not only presents a steep learning curve for the application programmers, but also makes it hard for the application programs to test these SQL queries.
Moreover, application programmers must learn enough about the various options and flags for several flavors of SQL in order to write a correctly functioning and optimal SQL query for that particular relational database. This approach of manually modifying the original SQL query to fit the requirements imposed by each relational database is also not optimal due to the reason that each and every customized SQL query must change if the original SQL query changes.
Thus, there is a need in the art for improved generation of SQL queries.